<?php 

echo "<a href='index.php'>Return to Main Menu</a><p style='font-size:18px'><i><b> ".$db_name." Table </b></i></p>";


//checking result
$queryNo = $_GET["t1"];
$queryVal = $_GET["searchvalue"]; 
//echo "You are Searching query: ".$queryNo."<br> with itemID=".$queryVal."<br>"; 

//setting up database connection
ini_set('display_errors', 'On');
$db = "w4111c.cs.columbia.edu:1521/adb";
$conn = oci_connect("jcz2105", "goodweather", $db);


//query DB and output result
//query1 processing----------------------------------------------------
if ($_GET['t1']=='q1' && !empty($queryVal) ) {
                
        //Select DISTINCT J.JEWELRYID, j.name, g.stone
        //From jewelry j, gemstoneinventory g, used u
        //Where u.gemitemid=2002 AND u.gemitemid = g.itemid AND u.jewelryid = j.jewelryid
        $query = "Select DISTINCT J.JEWELRYID, j.name, g.stone
                From jewelry j, gemstoneinventory g, used u
                Where u.gemitemid=".$_GET["searchvalue"].
                "  AND u.gemitemid = g.itemid AND u.jewelryid = j.jewelryid";
         
        // echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Jewelry ID</th>
                        <th>Name</th>
                        <th>Stone</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "<td>".$res[2]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);//close connection
}
//query2 processing----------------------------------------------
elseif ( $_GET['t1']=='q2' && !empty($queryVal) ) {

        //Select DISTINCT J.JEWELRYID, j.name, w.metal, w.gage
        //From jewelry j, wireinventory w, used u
        //Where u.wireitemid=1002 AND u.wireitemid = w.itemid AND u.jewelryid = j.jewelryid
        $query = "Select DISTINCT J.JEWELRYID, j.name, w.metal, w.gage
                From jewelry j, wireinventory w, used u
                Where u.wireitemid=".$_GET["searchvalue"].
                "  AND u.wireitemid = w.itemid AND u.jewelryid = j.jewelryid";
         
       // echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Jewelry ID</th>
                        <th>Name</th>
                        <th>Wire</th>
                        <th>Gage</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "<td>".$res[2]."</td>" ;
                echo "<td>".$res[3]."</td>" ;
                echo "</tr>";
        }
        echo "</table>";
        oci_commit($conn);//close connection
}
////query3 processing-----------------------------
elseif ( $_GET['t1']=='q3' && !empty($queryVal) ) {
                
                                           
        //Select DISTINCT i.itemid, i.purchase_price, o.suppliername
        //From inventory i, supplierorder o
        //Where i.itemid=1009 AND i.itemid=o.itemid;
        $query = "Select DISTINCT i.itemid, i.purchase_price, o.supplier_name
                          From inventory i, supplierorder o
                          Where i.itemid=".$_GET["searchvalue"]."AND i.itemid=o.itemid";
         
        //echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Item ID</th>
                        <th>Purchase Price</th>
                        <th>Supplier Name</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "<td>".$res[2]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
}
////query4 processing-----------------------------
elseif ( $_GET['t1']=='q4' && !empty($queryVal) ) {

        //Select DISTINCT c.clientid, c.name, J.name, o.dateoforder
        //From clientorder o, jewelry j, client c
        //Where o.clientid=3432435 AND o.clientid =c.clientid AND o.jewelryid = j.jewelryid	
        $query = " Select DISTINCT c.clientid, c.name, J.NAME, o.dateoforder
                                From clientorder o, jewelry j, client c
                                Where o.clientid=".$_GET["searchvalue"]. 
                                "AND o.clientid =c.clientid AND o.jewelryid = j.jewelryid";   
       // echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Client ID</th>
                        <th>Client Name</th>
                        <th>Jewelry Name</th>
                        <th>Date of Order</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "<td>".$res[2]."</td>" ;
                echo "<td>".$res[3]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
        echo "</table>";
}
////query5 processing-----------------------------
elseif ( $_GET['t1']=='q5' && (!empty($queryVal) || empty($queryVal) )) {

	/*Select J.JEWELRYID, j.name
	From JEWELRY J
	Where j.jewelryid IN (Select c.jewelryid
                      From client_order C
                      Group By c.jewelryid
                      Having Count(*)>= All (Select COUNT(*)
                                             From client_order c2
                                             Group BY c2.jewelryid))*/
	
 	    $query = "	Select J.JEWELRYID, j.name, j.description
				   	From JEWELRY J
					Where j.jewelryid IN (Select c.jewelryid
                     	 From clientorder C
               	    	 Group By c.jewelryid
                	     Having Count(*)>= All (Select COUNT(*)
                                             From clientorder c2
                                             Group BY c2.jewelryid))";
       //echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Jewelry ID</th>
                        <th>Jewelry Name</th>
                        <th>Jewelry Description</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "<td>".$res[2]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
        echo "</table>";
}
////query6 processing-----------------------------
elseif ( $_GET['t1']=='q6' && (!empty($queryVal) || empty($queryVal) )) {

	/*	select c.name, co.dateoforder, co.amount, j.name
		from clientorder co, jewelry j, client c
		where co.clientid = c.clientid and 
		j.jewelryid = co.jewelryid and co.dateoforder > '01-Mar-10';
	 * */
	if(empty($queryVal))
	    $query = "	select c.name, co.dateoforder, co.amount, j.name
					from clientorder co, jewelry j, client c
					where co.clientid = c.clientid and 
					            j.jewelryid = co.jewelryid and co.dateoforder > '01-Mar-10'"; 
	else if (!empty($queryVal) )
			    $query = "	select c.name, co.dateoforder, co.amount, j.name
							from clientorder co, jewelry j, client c
							where co.clientid = ".$_GET["searchvalue"]." and co.clientid = c.clientid and
							    j.jewelryid = co.jewelryid and co.dateoforder > '01-Mar-10'"; 
	
	
	
       //echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Client Name</th>
                        <th>Date of Order</th>
                        <th>Order Amount</th>
                        <th>Jewelry Name</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "<td>".$res[2]."</td>" ;
                echo "<td>".$res[3]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
        echo "</table>";
}
////query7 processing-----------------------------
elseif ( $_GET['t1']=='q7' && (!empty($queryVal) || empty($queryVal) )) {

	/*	Select J.JEWELRYID,  J.NAME
		From JEWELRY j
		Where J.QUANTITY < 10;
	 * */
    	$query = "	Select J.JEWELRYID,  J.NAME
					From JEWELRY j
					Where J.QUANTITY < ".$_GET["searchvalue"]; 
	   //echo $query;
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Jewelry ID</th>
                        <th>Jewelry Name</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
        echo "</table>";
}
////query8 processing-----------------------------
elseif ( $_GET['t1']=='q8' && (!empty($queryVal) || empty($queryVal) )) {

	/*	SELECT DISTINCT j.jewelryid, J.name
FROM jewelry j
WHERE j.jewelryid NOT IN (SELECT c.jewelryid
        FROM clientorder c
        GROUP BY c.jewelryid)

	 * */
    	$query = "	SELECT DISTINCT j.jewelryid, J.name
					FROM jewelry j
					WHERE j.jewelryid NOT IN (SELECT c.jewelryid
					        FROM clientorder c
					        GROUP BY c.jewelryid)";
    	
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Jewelry ID</th>
                        <th>Jewelry Name</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
        echo "</table>";
}
////query9 processing-----------------------------
elseif ( $_GET['t1']=='q9' && (!empty($queryVal) || empty($queryVal) )) {

	/*	Select Distinct u.jewelryid, s.supplier_name
From jewelry j, used u, supplierorder s, supplierorder ss 
Where j.jewelryid = u.jewelryid and u.wireitemid = s.itemid and
     u.gemitemid = ss.itemid  and s.itemid != ss.itemid and s.supplier_name = ss.supplier_name

	 * */
	 	$query = "	Select Distinct u.jewelryid, s.supplier_name
					From jewelry j, used u, supplierorder s, supplierorder ss 
					Where j.jewelryid = u.jewelryid and u.wireitemid = s.itemid and
     						u.gemitemid = ss.itemid  and s.itemid != ss.itemid 
     						and s.supplier_name = ss.supplier_name";
    	
        $stmt = oci_parse($conn, $query);
        oci_execute($stmt, OCI_DEFAULT);
        //search output
        echo "
                <p style='font-size:18px'><i><b> Search Result </b></i></p>
                <table border='1'>
                <tr>
                        <th>Jewelry ID</th>
                        <th>Jewelry Name</th>
                </tr>";
        while ($res = oci_fetch_row($stmt))
        {
                echo "<tr>";
                echo "<td>".$res[0]."</td>" ;
                echo "<td>".$res[1]."</td>" ;
                echo "</tr>";
        }
        oci_commit($conn);
        echo "</table>";
}

else 
	echo "not a valid input, try again";

echo '<P><A HREF="javascript:history.back()">Back</A></P>';

?>



<!-- 


<html>
<p>
You are Searching query:=<?php echo $_GET["t1"]; ?><br>
with itemID: <?php echo $_GET["stonesearch"]; ?> 



</p>
</html>

 -->